//201902104003邵潇漩
package bysj.dao;

import bysj.domain.ProfTitle;
import util.Condition;
import util.JdbcHelper;
import util.Pagination;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.List;
import java.util.TreeSet;
/**
 * @author 邵潇漩
 * @version 1.0  11-13-2020
 */

public final class ProfTitleDao {
    private static ProfTitleDao profTitleDao = null;

    private ProfTitleDao() {
    }

    public static ProfTitleDao getInstance() {
        if (profTitleDao == null){
            profTitleDao = new ProfTitleDao();
        }
        return profTitleDao;
    }

    public static Collection<ProfTitle> findAll( Pagination pagination, List<Condition> conditionList,Connection connection ) throws SQLException {
        Collection<ProfTitle> profTitles = new TreeSet<ProfTitle>();
        int totalNum = CommonDao.getInstance().count(connection,"profTitle");
        //创建查询的主语句
        StringBuilder sql_select = new StringBuilder("SELECT * FROM profTitle");
        //如果有条件，则生成条件子句，并附加到主语句后面
        if(conditionList!=null){
            String whereClause = Condition.toWhereClause(conditionList);
            sql_select.append(whereClause);
        }
        if(pagination!=null){
            String LimitClause = pagination.toLimitClause(totalNum);
            sql_select.append(LimitClause);
        }
        //获得查询的预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(sql_select.toString());
        //执行sql查询语句并获得结果集对象
        ResultSet resultSet = pstmt.executeQuery();
        //遍历结果集，根据元组创建对象，并添加到集合中
        while(resultSet.next()){
            profTitles.add(new ProfTitle(resultSet.getInt("id"),
                    resultSet.getString("description"),
                    resultSet.getString("no"),
                    resultSet.getString("remarks")
            ));
        }
        JdbcHelper.close(resultSet,pstmt);
        return profTitles;
    }

    public ProfTitle find(Integer id,Connection conn)throws SQLException{
        ProfTitle desiredProfTitle = null;
        String sql_select_id="SELECT*FROM ProfTitle WHERE id=?";
        //获得查询的预编译语句对象
        PreparedStatement pstmt = conn.prepareStatement(sql_select_id);
        pstmt.setInt(1,id);
        //执行sql查询语句并获得结果集对象
        ResultSet resultSet=pstmt.executeQuery();
        if(resultSet.next()){
            String description=resultSet.getString("description");
            String no = resultSet.getString("no");
            String remarks = resultSet.getString("remarks");
            desiredProfTitle=new ProfTitle(id,description,no,remarks);
        }
        JdbcHelper.close(resultSet,pstmt);
        //返回查询到的desiredProfTitle对象
        return desiredProfTitle;
    }

    public boolean update(ProfTitle profTitle,Connection conn)throws SQLException{
        //创建sql语句
        String updateProfTitle_sql = "UPDATE profTitle SET description=?, no=?, remarks=? WHERE id=?";
        //在该连接上创建预编译语句对象
        PreparedStatement pstmt = conn.prepareStatement(updateProfTitle_sql);
        //为预编译参数赋值
        pstmt.setString(1,profTitle.getDescription());
        pstmt.setString(2,profTitle.getNo());
        pstmt.setString(3,profTitle.getRemarks());
        pstmt.setInt(4,profTitle.getId());
        //执行预编译语句的executeUpdate方法,获得修改的记录行数
        int affectedRowNum=pstmt.executeUpdate();
        //关闭连接
        JdbcHelper.close(pstmt);
        //返回profTitle对象是否修改成功
        return affectedRowNum>0;
    }

    public boolean add(ProfTitle profTitle,Connection conn)throws SQLException {
        //创建sql语句
        String addProfTitle_sql="INSERT INTO ProfTitle "+"(description,no,remarks)value(?,?,?)";
        //在该连接上创建预编译语句对象
        PreparedStatement pstmt = conn.prepareStatement(addProfTitle_sql);
        //为预编译参数赋值
        pstmt.setString(1, profTitle.getDescription());
        pstmt.setString(2, profTitle.getNo());
        pstmt.setString(3, profTitle.getRemarks());
        int rowAffected =pstmt.executeUpdate();
        JdbcHelper.close(pstmt);
        return rowAffected>0;
    }

    public boolean delete(Integer id,Connection connection)throws SQLException{
        //创建sql语句
        String deleteProfTitle_sql = "DELETE FROM profTitle WHERE id=?";
        //在该连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(deleteProfTitle_sql);
        //为预编译参数赋值
        pstmt.setInt(1, id);
        //执行预编译语句的executeUpdate方法,获得删除的记录行数
        int affectedRowNum=pstmt.executeUpdate();
        //关闭连接
        JdbcHelper.close(null,pstmt);
        //如果删除记录的行数不为0，返回true,否则返回false
        return affectedRowNum>0;
    }
}

